15. 写入Excel文件

为什么需要将数据导出为Excel？

在金融数据分析和商业智能项目中，数据导出是工作流程的关键环节：

• 报告生成：向管理层或客户呈现分析结果
• 跨部门协作：与非技术人员（如业务部门、财务部门）共享数据
• 审计合规：保存分析过程的中间结果和最终输出
• 进一步分析：利用Excel的透视表、图表等功能进行探索性分析

Excel文件格式的技术演进

Pandas主要通过 openpyxl 引擎写入XLSX文件，通过 xlsxwriter 引擎实现高级格式化。

数据类型映射：Python → Excel

写入Excel时，Pandas自动进行数据类型转换：

特殊值处理：NaN → 缺失值标记，inf → #NUM! 错误或自定义表示

⭐ 平台实操：写入Excel文件

# ⚠️ 平台原始代码 - 请原样输入至教学平台（注释除外），平台才会判定答案正确
import numpy as np  # 导入NumPy数值计算库
import pandas as pd  # 导入Pandas数据分析库
import datetime as dt  # 导入日期时间处理模块

data=[[dt.datetime(2020,1,1, 10, 13), 2.222, 1, True],  # 定义列表data
      [dt.datetime(2020,1,2), np.nan, 2, False],  # 第二行数据（含缺失值NaN）
      [dt.datetime(2020,1,2), np.inf, 3, True]]  # 第三行数据（含无穷大inf）
df = pd.DataFrame(data=data,columns=["Dates", "Floats", "Integers", "Booleans"])  # 创建数据框df
df.index.name="index"  # 设置数据框索引列的名称

# 将数据框导出至Excel文件，指定工作表名和写入参数
df.to_excel("written_with_pandas1.xlsx", sheet_name="Output",
            startrow=1, startcol=1, index=True, header=True,  # 设置写入Excel时的起始行列位置和索引/表头选项
            na_rep="<NA>", inf_rep="<INF>")  #
            
with pd.ExcelWriter("written_with_pandas2.xlsx") as writer:  # 使用上下文管理器
  # 将数据框写入Sheet1工作表的第2行第2列位置
  df.to_excel(writer, sheet_name="Sheet1", startrow=1, startcol=1)
  # 将数据框再次写入Sheet1工作表的第11行位置
  df.to_excel(writer, sheet_name="Sheet1", startrow=10, startcol=1)
  df.to_excel(writer, sheet_name="Sheet2")  # 将数据框写入Excel文件

print(df)  # 输出数据框数据

                    Dates  Floats  Integers  Booleans
index                                                
0     2020-01-01 10:13:00   2.222         1      True
1     2020-01-02 00:00:00     NaN         2     False
2     2020-01-02 00:00:00     inf         3      True

to_excel 关键参数详解（一）

写入位置控制：

• startrow=1：数据从Excel第2行开始（第1行留给报告标题等）
• startcol=1：数据从Excel第2列开始（第1列留给行号或其他标识）
• index=True：是否写入行索引
• header=True：是否写入列名

这种灵活性允许在一个Excel文件中创建复杂的报表布局。

to_excel 关键参数详解（二）

特殊值处理参数：

• na_rep：控制缺失值的显示方式
  • 默认：空单元格
  • 本例：'<NA>' 明确标记缺失值
  • 常用值：'NA'、'-'、'NULL'
• inf_rep：控制无穷大的显示方式
  • 默认：Excel会显示 #NUM! 错误
  • 本例：'<INF>' 自定义表示
  • 常用值：'Infinity'、'∞'

ExcelWriter：上下文管理器模式

ExcelWriter 使用Python的上下文管理器（Context Manager）：

with pd.ExcelWriter('file.xlsx') as writer:
    # 执行写入操作
# 自动关闭文件，释放资源

核心优势：

• 自动资源管理：无论是否发生异常，文件都会正确关闭
• 异常安全：即使写入过程中出错，也能保证文件完整性
• 代码简洁：不需要显式调用 close() 方法

ExcelWriter：同一工作表多次写入

ExcelWriter不会覆盖已有内容，而是从指定位置开始写入：

with pd.ExcelWriter('report.xlsx') as writer:
    df.to_excel(writer, sheet_name='Sheet1', startrow=1)   # 第一块数据
    df.to_excel(writer, sheet_name='Sheet1', startrow=10)  # 第二块数据

这允许创建复杂的报表布局，例如：

• 标题区 → 数据表1 → 空行 → 数据表2

ExcelWriter：多工作表管理

将不同类型的数据放在不同工作表：

• Sheet1：原始数据
• Sheet2：计算指标
• Sheet3：图表数据

优势：数据分区清晰、可按工作表设置权限、大数据集分表提高加载速度。

to_excel vs ExcelWriter 对比

选择建议：简单导出用 to_excel，复杂报表用 ExcelWriter。

实际应用场景

• 财务报表导出：将计算好的财务指标导出为Excel，供审计使用
• 交易报告生成：每日交易结束后，生成交易汇总报告
• 数据存档：将处理后的历史数据保存为Excel，便于离线分析

大数据量处理策略

当处理大规模金融数据时（如百万级交易记录），需注意：

• Excel行数限制：XLS格式65,536行；XLSX格式1,048,576行
• 分批写入：将大数据集分成多个小文件
• 数据聚合：先汇总再导出，减小文件体积
• 格式选择：使用XLSB二进制格式（文件更小）

CSV vs Excel：如何选择？

选择建议：数据备份/迁移 → CSV；向非技术人员展示 → Excel；大数据量 → CSV或数据库。

本节小结

• to_excel() 是最基础的Excel导出方法，适合单表简单导出
• ExcelWriter 支持多工作表、同表多次写入等高级功能
• na_rep 和 inf_rep 参数处理特殊值的显示
• startrow 和 startcol 控制写入位置，实现灵活布局
• 大数据量场景需考虑Excel行数限制和性能优化